

/*
* @param @SalesOrderNo
* Returns: The status for a SalesOrder as
*	  Backlog or Cancelled or shipped or hold or all the mentioned options
*	  Like a single string
*/

ALTER           proc sp_get_sales_order_status 
@salesOrderNo varchar(10)
as
	begin
		print 'TEST'
		declare @results Table(Status varchar(60))
		declare @cst_status varchar(40), @cool_status varchar(20), @status varchar(60),@status_issued varchar(20)
		set @status = null
		declare @cancel_id int
		--select distinct CST_Status from customer_shipping_table where salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = 10378) and CST_Status <>'CANCELLED' and CST_Status <>'ISSUED'
		select distinct @cst_status=CST_Status from customer_shipping_table where salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = @salesOrderNo) and CST_Status <>'CANCELLED' and CST_Status <>'ISSUED'
		print @cst_status
		if @cst_status is not null
		begin
			print @cst_status
			set @status = @cst_status
		end
		
		select distinct @status_issued=CST_Status from customer_shipping_table where salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = @salesOrderNo) and CST_Status ='ISSUED'
		--select distinct CST_Status from customer_shipping_table where salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = 10378) and CST_Status ='ISSUED'
		if @status_issued is not null
		begin
			if @status is not null
			begin
				set @status = @status+', '+'BACKLOG'
				print @status 
			end
			else
			begin
				set @status = 'BACKLOG'
			end
		end
		--select distinct cool_status from customer_open_order_lineitem where salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = 10354) and Cool_Status ='HOLD'
		select distinct @cool_status =cool_status from customer_open_order_lineitem where salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = @salesOrderNo) and Cool_Status ='HOLD'
		
		if @cool_status is not null
		begin
			if @status is not null
			begin
				set @status = @status+', '+@cool_status
			end
			else
			begin
				set @status = @cool_status
			end
		end
		select distinct  @cancel_id=salesOrder_ID from customer_cancel_order_lineItem where salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = @salesOrderNo) 
		if @cancel_id is not null
		begin
			if @status is not null
			begin
				set @status = @status+', '+'CANCELLED'
			end
			else
			begin
				set @status = 'CANCELLED'
			end
		end

		

		
		declare @line_itm_id int, @cool_qty int, @qty_shipped int
		declare coolcursor cursor for
		select LineItem_ID, cool_Qty from customer_open_order_lineitem cool where cool_status = 'APPROVED' and
				salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = @salesOrderNo) 
				
		--select LineItem_ID, cool_Qty from customer_open_order_lineitem cool where cool_status = 'APPROVED' and salesOrder_Id = (select salesOrder_ID from sales_order where salesOrder_No = 10378) 
		open coolcursor
		fetch next from coolcursor into @line_itm_id, @cool_qty
		while @@fetch_status = 0
		begin
			select @qty_shipped=sum(CSLIT_Qty_Shipped) from customer_shipping_line_item_table where lineItem_id = @line_itm_id	
			
			--select sum(CSLIT_Qty_Shipped) from customer_shipping_line_item_table where lineItem_id = 240	
			if @qty_shipped is not null
			begin
				if @cool_qty > @qty_shipped
				
				--even if there is one such case then set the status accordingly and break from the loop
				begin
					if @status is not null
					begin
						
						set @status = @status+', '+ 'BACKLOG'
					end
	
					else
					begin
						set @status =  'BACKLOG'
					end
					break	
				end
			end
			else if @qty_shipped is null
			begin
				if @status is not null
				begin
					set @status = @status+', '+ 'BACKLOG'
				end
				else
				begin
					set @status =  'BACKLOG'
				end
				break
			end			
		fetch next from coolcursor into @line_itm_id, @cool_qty
		end
		close coolcursor
		deallocate coolcursor
			
		insert into @results values(@status)
	end
	
	select * from @results	





